﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class LinqToSqlDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridView1.DataSource = GetData();
            GridView1.DataBind();
        }
    }

    private object GetData()
    {
        var db = new TestDBDataContext(); // 打开"Test"数据库
                                          // 获取Table表中的数据
                                          //var results = db.Table; 
                                          //var results = from info in db.Table
                                          //              select info;

        //var results = from info in db.Table
        //              where info.Click_Number >= 1 
        //              select info;

        //var results = from info in db.Table
        //              orderby info.Publish_Time descending
        //              select info;

        //var results = from info in db.Table
        //              orderby info.Publish_Time descending
        //              select new
        //              {
        //                  info.Title,
        //                  info.Content,
        //                  info.Author
        //              };

        //var results = from info in db.Table
        //              orderby info.Publish_Time ascending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  正文 = info.Content,
        //                  作者 = info.Author
        //              };

        // 显示关联数据
        //var results = from info in db.Table
        //              orderby info.Publish_Time ascending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  正文 = info.Content,
        //                  分类 = info.Category.Name
        //              };

        //显示没有定义关系的关联数据
        var results = from info in db.Table
                        join category in db.Category on info.Category_Mark equals category.Mark
                        orderby info.Publish_Time ascending
                        select new
                        {
                            标题 = info.Title,
                            正文 = info.Content,
                            分类 = category.Name
                        };

        // Linq的方法
        //var results = db.Table.Select(res => res.Title);

        //var results = db.Table.Sum(res => res.Click_Number);
        //var results = db.Table.Count();
        //var results = db.Table.Take(1);


        Debug.WriteLine(results);

        return results;
    }

    protected void Btn_Keyword_Click(object sender, EventArgs e)
    {
        string searchKey = Tb_Keyword.Text.Trim();
        GridView1.DataSource = GetData(searchKey);
        GridView1.DataBind();
    }

    private object GetData(string searchKey)
    {
        var db = new TestDBDataContext();

        // 以关键字开始
        //var results = from info in db.Table
        //              where info.Title.StartsWith(searchKey) 
        //              select info;

        // 以关键字结束
        //var results = from info in db.Table
        //              where info.Title.EndsWith(searchKey)
        //              select info;

        // 包含关键字
        //var results = from info in db.Table
        //              where info.Title.Contains(searchKey)
        //              select info;

        // 使用Like方法
        var results = from info in db.Table
                      where System.Data.Linq.SqlClient.SqlMethods.Like(info.Title, "%" + searchKey + "%") 
                      select info;

        Debug.WriteLine(results);
        return results;
    }
}